package com.classes;

import java.util.ArrayList;
import java.util.TreeMap;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Blob;

import org.json.simple.*;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.util.Constants;

public class CasemastFactory {
	private static String message;
	
	public static String getMessage() {
		return "[CasemastFactory] " + CasemastFactory.message; 
	}
	
	public static String getStartStatus(int branch){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from statusmast "
					+ "where s_current = '_START_' "
					+ "and s_branch = ?";
			
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, branch);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						String next = "";
						String sep = "";
						while(resultSet.next()){
							next += sep + resultSet.getString("s_next");
							sep = ",";
						}
						
						
						return next;
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	public static String getStartStatus(int branch, int branch2){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from statusmast "
					+ "where s_current = '_START_' " 
					+ "and (s_branch < ? and s_branch > ?) "
					+ "and s_next not in ('TERMINAL REQUEST SUBMISSION')";
					

			
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, branch);
					statement.setInt(2, branch2);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						String next = "";
						String sep = "";
						while(resultSet.next()){
							next += sep + resultSet.getString("s_next");
							sep = ",";
						}
						
						
						return next;
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	
	public static ArrayList<String> getStatusFilter(int branch){
		String startStatus = CasemastFactory.getStartStatus(branch);
		if(startStatus == null){
			return null;
		}
		
		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";
		
		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select distinct s_next from statusmast "
						+ "where s_next not in (" + paramStr + ", ?) "
						+ "and s_branch = ? "
						+ "order by s_next asc";
				
				
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					int paramIndex = 1;
					for(String s : startArr) {
						statement.setString(paramIndex++, s);
					}
					
					statement.setString(paramIndex++, "*");
					statement.setInt(paramIndex++, branch);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						ArrayList<String> next = new ArrayList<String>();
						while(resultSet.next()){
							next.add(resultSet.getString("s_next"));
						}
						
						return next;
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<String> getStatusFilter(int branch, int branch2){
		String startStatus = CasemastFactory.getStartStatus(branch, branch2);
		if(startStatus == null){
			return null;
		}
		
		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";
		
		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select distinct s_next from statusmast "
						+ "where s_next not in (" + paramStr + ", ?) "
						+ "and (s_branch < ? and s_branch > ?)"
						+ "order by s_next asc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					int paramIndex = 1;
					for(String s : startArr) {
						statement.setString(paramIndex++, s);
					}
					
					statement.setString(paramIndex++, "*");
					statement.setInt(paramIndex++, branch);
					statement.setInt(paramIndex++, branch2);

					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						ArrayList<String> next = new ArrayList<String>();
						while(resultSet.next()){
							next.add(resultSet.getString("s_next"));
						}
						
						return next;
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<Casemast> getUnattendedCasemasts(){
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select casemast.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "where c_modified_date < sysdate - 90 ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							Casemast c = CasemastFactory.parseResultSet(resultSet);
							cArr.add(c);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	public static ArrayList<Casemast> getPendingCasemasts(String groupCode, TreeMap<String, String> search) {
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();
		
		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}
		
		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";
		
		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("case_id") && search.get("case_id").length() > 0){
						q_search += "and c_reference_no = ? ";
					}
					
					if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
						q_search += "and upper(c_merchant_details) like ? ";
					}
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				String sql = "select casemast.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "left join tbltree on child_id = c_group_code "
						+ "where parent_id = ? and c_status in (" + paramStr + ") "
						+ q_search
						+ "order by c_id asc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, groupCode);
					
					int last_index = 1;
					for(String s : startArr) {
						statement.setString(++last_index, s);
					}
					
					if(search != null) {
						if(search.containsKey("case_id") && search.get("case_id").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("case_id").toUpperCase());
						}
						
						if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
							++last_index;
							statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
						}
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							Casemast c = CasemastFactory.parseResultSet(resultSet);
							cArr.add(c);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	public static ArrayList<Casemast> getActiveCasemasts(String groupCode, TreeMap<String, String> search) {
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();
		
		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}
		
		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";
		
		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("case_id") && search.get("case_id").length() > 0){
						q_search += "and c_reference_no = ? ";
					}
					
					if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
						q_search += "and upper(c_merchant_details) like ? ";
					}
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("status") && search.get("status").length() > 0){
						q_search += "and c_status = ? ";
					}
				}
				
				String sql = "select casemast.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "left join tbltree on child_id = c_group_code "
						+ "where parent_id = ? "
						+ "and c_status not in (" + paramStr + ") "
						+ "and c_status not like 'CLOSE%' "
						+ q_search
						+ "order by c_id desc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, groupCode);
					
					int last_index = 1;
					for(String s : startArr) {
						statement.setString(++last_index, s);
					}
					
					if(search != null) {
						
						if(search.containsKey("case_id") && search.get("case_id").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("case_id").toUpperCase());
						}
						
						if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
							++last_index;
							statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
						}
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("status") && search.get("status").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("status"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							Casemast c = CasemastFactory.parseResultSet(resultSet);
							cArr.add(c);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	public static ArrayList<Casemast> getCasemasts(String groupCode, TreeMap<String, String> search) {
		ArrayList<Casemast> cArr = new ArrayList<Casemast>();
		
		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}
		
		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";
		
		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("status") && search.get("status").length() > 0){
						q_search += "and c_status = ? ";
					}
					
					if(search.containsKey("case_id") && search.get("case_id").length() > 0){
						q_search += "and c_reference_no = ? ";
					}
					
					if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
						q_search += "and upper(c_merchant_details) like ? ";
					}
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
						q_search += "and c_submit_date >=  to_date(?, 'YYYY-MM-DD') and c_submit_date < to_date(?, 'YYYY-MM-DD') + 1 ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				String sql = "select casemast.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "left join tbltree on child_id = c_group_code "
						+ "where parent_id = ? and c_status not in (" + paramStr + ") "
						+ q_search
						+ (q_search.isEmpty() ? "and rownum <= 1000 " : "")
						+ "order by c_id desc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, groupCode);
					
					int last_index = 1;
					for(String s : startArr) {
						statement.setString(++last_index, s);
					}
					
					if(search != null) {
						if(search.containsKey("status") && search.get("status").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("status"));
						}
						
						if(search.containsKey("case_id") && search.get("case_id").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("case_id").toUpperCase());
						}
						
						if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
							++last_index;
							statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
						}
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("submit_date"));
							
							++last_index;
							statement.setString(last_index, search.get("submit_date"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							Casemast c = CasemastFactory.parseResultSet(resultSet);
							cArr.add(c);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getPendingVisaVMTSCasemasts(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}
		
		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";
		
		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("case_id") && search.get("case_id").length() > 0){
						q_search += "and c_reference_no = ? ";
					}
					
					if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
						q_search += "and upper(c_merchant_details) like ? ";
					}
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(q_search.length() <= 0){
						q_search = "and c_visa_vmts_batch is null ";
					}
				}
				else {
					q_search = "and c_visa_vmts_batch is null ";
				}
				
				String sql = "select casemast.*, caseoutlet.*, role_name, b_created_date "
						+ "from casemast "
						+ "left join visavmtsbatch on c_visa_vmts_batch = b_id "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "left join caseoutlet on co_case_id = c_id "
						+ "where c_status not in (" + paramStr + ") "
						+ "and c_status not like 'CLOSE%' "
						+ q_search
						+ "order by c_id asc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					int last_index = 0;
					for(String s : startArr) {
						statement.setString(++last_index, s);
					}
					
					if(search != null) {
						if(search.containsKey("case_id") && search.get("case_id").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("case_id").toUpperCase());
						}
						
						if(search.containsKey("merchant_name") && search.get("merchant_name").length() > 0){
							++last_index;
							statement.setString(last_index, "%\"REGISTERED_NAME\":\"" + search.get("merchant_name").toUpperCase() + "%");
						}
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = CasemastFactory.parseOutletResultSet(resultSet);
							cArr.add(c);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static Casemast getCaseMast(String groupCode, long id){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			try {
				String sql = "select casemast.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "left join tbltree on child_id = c_group_code "
						+ "where parent_id = ? and c_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, groupCode);
					statement.setLong(2, id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						
						if(resultSet.next()){
							Casemast c = CasemastFactory.parseResultSet(resultSet);
							return c;
						} 
						else {
							CasemastFactory.message = "Application not found.";
							return null;
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage(); 
			return null;
		}
	}
	
	public static Casemast getCaseMast(long id){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
	        
			try {
				String sql = "select casemast.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "where c_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							Casemast c = CasemastFactory.parseResultSet(resultSet);
							return c;
						} 
						else {
							CasemastFactory.message = "Application not found.";
							return null;
						}
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	public static Casemast getCaseMastByRefNo(String ref_no){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select casemast.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "where c_reference_no = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, ref_no);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							Casemast c = CasemastFactory.parseResultSet(resultSet);
							return c;
						} 
						else {
							CasemastFactory.message = "Application not found.";
							return null;
						}
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	protected static Casemast parseResultSet(ResultSet resultSet) throws Exception{
		JSONObject merchant_json = (JSONObject)JSONValue.parse(resultSet.getString("c_merchant_details"));
		JSONArray avg_deposit_json = (JSONArray) merchant_json.get("curr_avg_deposit_dtl");
		
		Merchant m = new Merchant();
		m.setId(Long.parseLong(resultSet.getString("c_merchant_id")));
		
		m.setOwnershipStatus(merchant_json.get("ownership_status").toString());
		m.setRegisteredName(merchant_json.get("registered_name").toString());
		m.setRegisteredNo(merchant_json.get("registered_no").toString());
		m.setDesc(merchant_json.get("desc").toString());
		m.setMCCCode(merchant_json.get("mcc_code").toString());
		m.setPaidUpCapital(merchant_json.get("paid_up_capital").toString());
		m.setRecallBarcode(merchant_json.get("recall_barcode").toString());

		try{
			m.setGst_registration(merchant_json.containsKey("gst_registration") ? merchant_json.get("gst_registration").toString() : "");
		}catch (Exception e1){
			System.out.println("set gst_registration error");
		}
		try{
			m.setRmcd_approval_no(merchant_json.containsKey("rmcd_approval_no") ? merchant_json.get("rmcd_approval_no").toString() : "");
		}catch (Exception e1){
			System.out.println("set rmcd_approval_no error");
		}
		
		m.setYearInBusiness(merchant_json.get("year_in_business").toString());
		m.setAvgTicketSize(merchant_json.get("avg_ticket_size").toString());
		m.setCurrBank(merchant_json.get("curr_bank").toString());
		m.setCurrAccNo(merchant_json.get("curr_acc_no").toString());
		m.setCurrAvgDeposit(merchant_json.get("curr_avg_deposit").toString());
		m.setCurrAvgDepositDtl(avg_deposit_json.toString());
		m.setAmexAuthSign(resultSet.getString("c_amex_auth_sign"));
		m.setMatchTrace(resultSet.getString("c_match_trace"));
		
		Casemast c = new Casemast();
		
		c.setId(Long.parseLong(resultSet.getString("c_id")));
		c.setGroupCode(resultSet.getString("c_group_code"));
		c.setGroupName(resultSet.getString("role_name"));
		c.setAction(resultSet.getString("c_action"));
		c.setStatus(resultSet.getString("c_status"));
		c.setAction2(resultSet.getString("c_action2"));
		c.setStatus2(resultSet.getString("c_status2"));
		c.setBranch2(resultSet.getString("c_branch2"));
		c.setType(resultSet.getString("c_type"));
		c.setAcquirer(resultSet.getString("c_acquirer"));
		c.setMerchant(m);
		c.setCreatedDate(((Timestamp) resultSet.getTimestamp("c_created_date")).getTime());
		c.setCreatedBy(resultSet.getString("c_created_by"));
		
		if(resultSet.getTimestamp("c_submit_date") != null){
			c.setSubmitDate(((Timestamp) resultSet.getTimestamp("c_submit_date")).getTime());
		}
		
		c.setReferenceNo(resultSet.getString("c_reference_no"));
		c.setLastReferenceNo(resultSet.getString("c_last_reference_no"));
		c.setCountryCode(resultSet.getString("c_country_code"));
		c.setDocumentChecklist(resultSet.getString("c_document_checklist"));
		c.setBestPractice(resultSet.getString("c_best_practice"));
		c.setExternalRemark(resultSet.getString("c_ext_remark"));
		c.setExternalRefNo(resultSet.getString("c_ext_ref_no"));
		c.setResellerLocation(resultSet.getString("c_reseller_location"));
		c.setExternalCode(resultSet.getString("c_ext_code"));
		c.setExportedExternalCode(resultSet.getString("c_export_ext_code"));
		
		return c;
	}
	
	protected static TreeMap<String, String> parseOutletResultSet(ResultSet resultSet) throws Exception{
		JSONObject merchant_json = (JSONObject)JSONValue.parse(resultSet.getString("c_merchant_details"));
		
		TreeMap<String, String> c = new TreeMap<String, String>();
		
		c.put("id", resultSet.getString("c_id"));
		c.put("merchant_id", resultSet.getString("c_merchant_id"));
		c.put("reference_no", resultSet.getString("c_reference_no"));
		c.put("visa_vmts_batch", resultSet.getString("c_visa_vmts_batch") == null ? "0" : resultSet.getString("c_visa_vmts_batch"));
		c.put("country_code", resultSet.getString("c_country_code"));
		c.put("registered_name", merchant_json.get("registered_name").toString());
		c.put("registered_no", merchant_json.get("registered_no").toString());
		c.put("mcc_code", merchant_json.get("mcc_code").toString());
		c.put("match_trace", resultSet.getString("c_match_trace"));
		
		c.put("outlet_id", "");
		c.put("trading_name", "");
		c.put("address", "");
		c.put("email", "");
		c.put("tel_no", "");
		
		JSONObject outlet_obj = new JSONObject();
		if(resultSet.getString("co_outlet_details") != null){
			outlet_obj = (JSONObject) JSONValue.parse(resultSet.getString("co_outlet_details"));
			
			c.put("outlet_id", resultSet.getString("co_outlet_id"));
			c.put("trading_name", outlet_obj.get("trading_name").toString());
			c.put("address", resultSet.getString("co_address"));
			c.put("email", outlet_obj.get("email").toString());
			c.put("tel_no", outlet_obj.get("telephone_no").toString());
		}
		
		
		c.put("vmts_batch_date", resultSet.getString("b_created_date"));
		
		if(resultSet.getString("b_created_date") != null){
			Long t = resultSet.getTimestamp("b_created_date").getTime();
			c.put("vmts_batch_date", t.toString());
		}
		
		return c;
	}
	
	public static long action_submit(int case_id, TreeMap<String, String> post, String user, int branch){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "{? = call STATUSACTION.action_next(?, ?, ?, ?, ?, ?)}";
				
				CallableStatement cstmt = conn.prepareCall(sql);
				
				try {
					cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
		            cstmt.setInt(2, case_id);
					cstmt.setString(3, post.get("action"));
					cstmt.setString(4, post.get("note"));
					cstmt.setString(5, post.get("assign_to"));
					cstmt.setString(6, user);
					cstmt.setInt(7, branch);
					
		            cstmt.executeQuery();
		            
		            long rtn = cstmt.getLong(1);
					
					
					if(rtn > 0){
						return rtn;
					} else {
						CasemastFactory.message = "Error occured while executing STATUSACTION.action_next.";
						return -1;
					}
					
				} finally {
					cstmt.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return -1;
		}
	}
	
	public static boolean extremark_submit(int case_id, TreeMap<String, String> post, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "update casemast set c_ext_remark = ?, c_modified_date = sysdate, c_modified_by = ? where c_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, post.get("external_remark"));
					statement.setString(2, user);
					statement.setLong(3, case_id);
					
		            statement.execute();
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		}
	}
	
	public static boolean best_practice_submit(int case_id, TreeMap<String, String> post, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "update casemast set c_best_practice = ?, c_modified_date = sysdate, c_modified_by = ? where c_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, post.get("best_practice"));
					statement.setString(2, user);
					statement.setLong(3, case_id);
					
		            statement.execute();
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		}
	}
	
	public static boolean document_submit(int case_id, TreeMap<String, String> post, byte[] data, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "insert into casedoc(cd_case_id, cd_name, cd_data, cd_type, cd_length, cd_ref_no, cd_created_by, cd_created_date) "
					+ "values(?, ?, empty_blob(), ?, ?, ?, ?, sysdate)";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, case_id);
					statement.setString(2, post.get("file_name"));
					statement.setString(3, post.get("file_type"));
					statement.setLong(4, Long.parseLong(post.get("file_size")));
					statement.setString(5, post.get("ref_no").length() > 0 ? post.get("ref_no") : null);
					statement.setString(6, user);
					
		            statement.execute();
	            
				} finally {
					statement.close();
				}
				
				
				Long doc_id = null;
				ResultSet rs;
				Statement statement2 = conn.createStatement();
				
				try {
					rs = statement2.executeQuery("SELECT SEQ_CASEDOC.CURRVAL FROM DUAL");
					
					try {
						if (rs.next()) {
							doc_id = new Long(rs.getLong(1));
						}
						
						
					} finally {
						rs.close();
					}
				} finally {
					statement2.close();
				}
				
				
				sql = "select cd_data from casedoc where cd_id = ? for update";
				
				statement = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				
				try {
					statement.setLong(1, doc_id);
					
					rs = statement.executeQuery();
					
					try {
						if (rs.next()) {
							Blob blob = rs.getBlob("cd_data");
							blob.truncate(0);
							blob.setBytes(1, data);
							rs.updateBlob("cd_data", blob);
							rs.updateRow();
						}
						
						return true;
						
					} finally {
						rs.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		}
	}
	
	public static boolean document_delete(int doc_id, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "delete from casedoc where cd_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, doc_id);
		            statement.execute();
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		}
	}
	
	
	public static boolean visa_vmts_batch_submit(String case_json_arr, String user){
		try {
			JSONArray arr = (JSONArray) JSONValue.parse(case_json_arr);
			if(arr.size() <= 0){
				return true;
			}
			
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "insert into visavmtsbatch(b_created_by, b_created_date) "
					+ "values(?, sysdate)";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, user);
		            statement.execute();
				} finally {
					statement.close();
				}
	            
				
				Long batch_id = null;
				Statement statement2 = conn.createStatement();
				
				try {
					ResultSet rs = statement2.executeQuery("SELECT SEQ_VISAVMTSBATCH.CURRVAL FROM DUAL");
					try {
						if (rs.next()) {
							batch_id = new Long(rs.getLong(1));
						}
					} finally {
						rs.close();
					}
				} finally {
					statement2.close();
				}
				
				
				
				sql = "update casemast set c_visa_vmts_batch = ? where c_id = ?";
				
				statement = conn.prepareStatement(sql);
				
				try {
					for(int i=0; i<arr.size(); i++){
						long case_id = Long.parseLong(arr.get(i).toString());
						
						statement.setLong(1, batch_id);
						statement.setLong(2, case_id);
						statement.execute();
					}
				
				} finally {
					statement.close();
				}
			
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			CasemastFactory.message = e.getMessage();
			return false;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getVisaVMTSBatch(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
						q_search += "where b_created_date >=  to_date(?, 'YYYY-MM-DD') and b_created_date < to_date(?, 'YYYY-MM-DD') + 1 ";
					}
				}
				
				
				String sql = "select * from visavmtsbatch "
						+ q_search
						+ "order by b_id desc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					if(search != null) {
						int last_index = 0;
						
						if(search.containsKey("submit_date") && search.get("submit_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("submit_date"));
							
							++last_index;
							statement.setString(last_index, search.get("submit_date"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							c.put("id", resultSet.getString("b_id"));
							c.put("created_date", resultSet.getString("b_created_date"));
							c.put("created_by", resultSet.getString("b_created_by"));
							
							Long t = resultSet.getTimestamp("b_created_date").getTime();
							c.put("created_date", t.toString());
							
							cArr.add(c);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			e.printStackTrace();
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
	
	
	public static ArrayList<TreeMap<String, String>> getVisaVMTSBatchDetails(long batch_id) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select casemast.*, caseoutlet.*, visavmtsbatch.*, role_name from casemast "
						+ "left join tblaccessrole on c_group_code = role_code "
						+ "left join caseoutlet on co_case_id = c_id "
						+ "left join visavmtsbatch on c_visa_vmts_batch = b_id "
						+ "where c_visa_vmts_batch = ? "
						+ "order by c_id asc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, batch_id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = CasemastFactory.parseOutletResultSet(resultSet);
							
							c.put("batch_id", resultSet.getString("b_id"));
							c.put("batch_created_date", resultSet.getString("b_created_date"));
							c.put("batch_created_by", resultSet.getString("b_created_by"));
							
							Long t = resultSet.getTimestamp("b_created_date").getTime();
							c.put("batch_created_date", t.toString());
							
							cArr.add(c);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			e.printStackTrace();
			CasemastFactory.message = e.getMessage();
			return null;
		}
	}
}